<?php

namespace app\crm\model;

use think\Db;
use app\admin\model\Common;
use think\Request;
use think\Validate;

class Finance extends Common
{
    protected $name = 'crm_finance';
    protected $createTime = 'created_time';
    protected $updateTime = '';
    protected $autoWriteTimestamp = true;
    private $check_status=[0=>"待审",1=>"审批中",2=>"通过",3=>"失败",4=>"撤销"];

    /**
     * [getDataList 财务list]
     * @author Michael_xu
     * @param     [string]                   $map [查询条件]
     * @param     [number]                   $page     [当前页数]
     * @param     [number]                   $limit    [每页数量]
     * @return    [array]                    [description]
     */
    public function getDataList($request)
    {
        $request = $this->fmtRequest( $request );
        $map = $request['map'] ? : [];
        if($map["search"]){
            $map["finance.department"]=["like",'%'.$map["search"].'%'];
        }
        $user_id=$map["user_id"];
        unset($map["search"]);
        unset($map["user_id"]);
        if(isset($map["check_status"])){//审核状态
            $check_status=["待审核"=>"0","通过"=>"2","审核中"=>"1","撤销"=>"4","失败"=>"3"];
            $map["check_status"]["value"]=$check_status[$map["check_status"]["value"]];
        }
        //高级筛选
        $map = where_arr1($map, 'crm', 'finance', 'index');
        //财务编号、原单编号、订单类型、所属部门、核算月份、审核状态、发起人、合同编号、业绩类型
        if(isset($map["finance.contract_num"])){//合同编号
            $map["contract.num"]=$map["finance.contract_num"];
            unset($map["finance.contract_num"]);
        }
        if(isset($map["finance.create_user_name"])){//发起人
            $map["admin.realname"]=["like","%".$map["finance.create_user_name"][1]."%"];
            unset($map["finance.create_user_name"]);
        }
        if(isset($map["finance.user_department"])){//发起人部门
            $map["structure.name"]=["like","%".$map["finance.user_department"][1]."%"];
            unset($map["finance.user_department"]);
        }
        if(isset($map["finance.customer_name"])){//客户名称
            $map["customer.name"]=["like","%".$map["finance.customer_name"][1]."%"];
            unset($map["finance.customer_name"]);
        }
        if(isset($map["finance.crm_oonnxn"])){//原销售
            $map["contract.crm_oonnxn"]=["like","%".$map["finance.crm_oonnxn"][1]."%"];
            unset($map["finance.crm_oonnxn"]);
        }
        if(isset($map["finance.product_name"])){//产品类型
            $map["product_c.name"]=["like","%".$map["finance.product_name"][1]."%"];
            unset($map["finance.product_name"]);
        }

        if(isset($map["finance.expense_type"])){//报销类型
            $map["finance.expense_type"]=$map["finance.expense_type"][1];
        }
        if(isset($map["finance.created_time"])){//创建时间
            $map["finance.created_time"][1][0]=date("Y-m-d H:i:s",$map["finance.created_time"][1][0]);
            $map["finance.created_time"][1][1]=date("Y-m-d H:i:s",$map["finance.created_time"][1][1]);
        }
        if(isset($map["finance.check_time"])){//审核时间
            $map["finance.check_time"][1][0]=date("Y-m-d H:i:s",$map["finance.check_time"][1][0]);
            $map["finance.check_time"][1][1]=date("Y-m-d H:i:s",$map["finance.check_time"][1][1]);
        }

        //财务和超级管理员可以看所有，其他只能看对应自己和下级的  customer_name
        $isFinance=db("admin_access")->where(["user_id"=>$user_id,"group_id"=>9])->find();//是否是财务
        $isButler = db('admin_access')->where(["user_id"=>$user_id,"group_id"=>32])->find();//管家
        if(!$isButler && !$isFinance && $user_id!=1){
            $childIds=$this->getChildIds($user_id);
            $childIds[]=$user_id;
            $map["finance.create_user_id"]=["in",$childIds];
        }

        // 管理员
        $user = db('admin_user')->where(['id' => $user_id])->find();
        if($user['structure_id'] == 38) {
            $childIds=$this->getChildId($user_id);
            $childIds[]=$user_id;
            $map["finance.create_user_id"]=["in",$childIds];
        }

        $list = db('crm_finance')
            ->alias('finance')
            ->join('admin_user admin',"admin.id=finance.create_user_id","left")
            ->join('__CRM_CONTRACT__ contract',"contract.contract_id=finance.contract_id","left")
            ->join('__CRM_CUSTOMER__ customer',"customer.customer_id=finance.customer_id","left")
            ->join('__ADMIN_STRUCTURE__ structure',"structure.id=admin.structure_id","left")
            ->join('__CRM_CONTRACT_PRODUCT__ c_product',"c_product.contract_id=contract.contract_id","left")
            ->join('__CRM_PRODUCT__ product',"product.product_id=c_product.product_id","left")
            ->join('__CRM_PRODUCT_CATEGORY__ product_c',"product_c.category_id=product.category_id","left")
            ->where($map)
            ->limit(($request['page']-1)*$request['limit'], $request['limit'])
            ->field('finance.*,admin.id as admin_id,admin.username admin_username,admin.realname create_user_name
            ,contract.name contract_name,contract.num contract_num,contract.crm_oonnxn,contract.crm_ddisob,product_c.name product_name
            ,contract.crm_grvgkz,contract.remark contract_remark,contract.crm_wvgjii,contract.crm_wyffaz,contract.accounting_start_time,contract.accounting_end_time,contract.crm_yfvcld,
            contract.money cmoney,customer.name customer_name,structure.name user_department,product.product_id')
            ->order("finance.finance_id desc,finance.created_time desc")
            ->select();
        foreach ($list as $key => &$value) {
            if($value['create_user_id'] != $user_id && $user['structure_id'] == 38) {
                $value['customer_name'] = '***********';
            }
            if($value['order_type'] == '报销') {
                $expence = db("crm_expence")->where('examine_id',$value['relation_id'])->find();
                $expence_detail = db("crm_expence_detail")->where('expence_id',$expence['id'])->find();
                if($expence_detail) {
                    $value['crm_oeseng'] = $expence_detail['cost_money'];
                }
            }
        }

        $total_data = db('crm_finance')
            ->alias('finance')
            ->join('admin_user admin',"admin.id=finance.create_user_id","left")
            ->join('__CRM_CONTRACT__ contract',"contract.contract_id=finance.contract_id","left")
            ->join('__CRM_CUSTOMER__ customer',"customer.customer_id=finance.customer_id","left")
            ->join('__ADMIN_STRUCTURE__ structure',"structure.id=admin.structure_id","left")
            ->join('__CRM_CONTRACT_PRODUCT__ c_product',"c_product.contract_id=contract.contract_id","left")
            ->join('__CRM_PRODUCT__ product',"product.product_id=c_product.product_id","left")
            ->join('__CRM_PRODUCT_CATEGORY__ product_c',"product_c.category_id=product.category_id","left")
            ->where($map)->field("sum(contract.money) contract_money_total,sum(finance.order_num) single_total,sum(finance.money) receivables_money_total,sum(finance.calculate) calculate_total,sum(finance.crm_oeseng) crm_oeseng_total")->find();
        /*$contract_id_arr = db('crm_finance')
            ->alias('finance')
            ->join('admin_user admin',"admin.id=finance.create_user_id","left")
            ->join('__CRM_CONTRACT__ contract',"contract.contract_id=finance.contract_id","left")
            ->join('__CRM_CUSTOMER__ customer',"customer.customer_id=finance.customer_id","left")
            ->join('__ADMIN_STRUCTURE__ structure',"structure.id=admin.structure_id","left")
            ->join('__CRM_CONTRACT_PRODUCT__ c_product',"c_product.contract_id=contract.contract_id","left")
            ->join('__CRM_PRODUCT__ product',"product.product_id=c_product.product_id","left")
            ->join('__CRM_PRODUCT_CATEGORY__ product_c',"product_c.category_id=product.category_id","left")
            ->where($map)->group("finance.contract_id")->column("contract.contract_id");
        $total_data1=db("crm_contract")->where(["contract_id"=>["in",$contract_id_arr]])->field("sum(money) contract_money_total,sum(crm_yfvcld) single_total")->find();
        $total_data=array_merge($total_data,$total_data1);*/
        if($list){
            foreach ($list as $k=>$v){
                if($v['order_type'] == '回款') {
                    $list[$k]["order_num"] = db('crm_receivables')->where('receivables_id',$v['relation_id'])->find()['crm_ahokzi'];
                }
                
                $list[$k]["check_status"]=$this->check_status[$v["check_status"]];
                if($v['crm_wvgjii'] == '记账') {
                    $confirmation = db("crm_contract_confirmation")->where(['contract_id'=>$v['contract_id'],'product_id'=>$v['product_id'],'name'=>'服务时间'])->find();
                    if(!empty($confirmation)){
                       $value_data = json_decode($confirmation['value_data']); 
                       $list[$k]["accounting_start_time"]=$value_data[0];
                       $list[$k]["accounting_end_time"]=$value_data[1];
                    }
                    
                } else {
                    $list[$k]["accounting_start_time"]=$v["accounting_start_time"]?date("Y-m-d H:i:s",$v["accounting_start_time"]):"";
                    $list[$k]["accounting_end_time"]=$v["accounting_end_time"]?date("Y-m-d H:i:s",$v["accounting_end_time"]):"";
                }
            }
        }
        $dataCount =  db('crm_finance')
            ->alias('finance')
            ->join('admin_user admin',"admin.id=finance.create_user_id","left")
            ->join('__CRM_CONTRACT__ contract',"contract.contract_id=finance.contract_id","left")
            ->join('__CRM_CUSTOMER__ customer',"customer.customer_id=finance.customer_id","left")
            ->join('__ADMIN_STRUCTURE__ structure',"structure.id=admin.structure_id","left")
            ->join('__CRM_CONTRACT_PRODUCT__ c_product',"c_product.contract_id=contract.contract_id","left")
            ->join('__CRM_PRODUCT__ product',"product.product_id=c_product.product_id","left")
            ->join('__CRM_PRODUCT_CATEGORY__ product_c',"product_c.category_id=product.category_id","left")
            ->where($map)
            ->count('1');
        $data = [];
        $data['list'] = $list;
        $data["totalData"]=$total_data;
        $data['dataCount'] = $dataCount ? : 0;
        return $data;
    }

    /**
     * @param $request
     * @return array
     * 获取所有财务数据用于导出
     */
    public function getListDataAll($request)
    {
        $request = $this->fmtRequest( $request );
        $map = $request['map'] ? : [];
        if($map["search"]){
            $map["finance.department"]=["like",'%'.$map["search"].'%'];
        }
        $user_id=$map["user_id"];
        unset($map["search"]);
        unset($map["user_id"]);
        if(isset($map["check_status"])){//审核状态
            $check_status=["待审核"=>"0","通过"=>"2","审核中"=>"1","撤销"=>"4","失败"=>"3"];
            $map["check_status"]["value"]=$check_status[$map["check_status"]["value"]];
        }
        //高级筛选
        $map = where_arr1($map, 'crm', 'finance', 'index');
        //财务编号、原单编号、订单类型、所属部门、核算月份、审核状态、发起人、合同编号、业绩类型
        if(isset($map["finance.contract_num"])){//合同编号
            $map["contract.num"]=$map["finance.contract_num"];
            unset($map["finance.contract_num"]);
        }
        if(isset($map["finance.create_user_name"])){//发起人
            $map["admin.realname"]=["like","%".$map["finance.create_user_name"][1]."%"];
            unset($map["finance.create_user_name"]);
        }
        if(isset($map["finance.user_department"])){//发起人部门
            $map["structure.name"]=["like","%".$map["finance.user_department"][1]."%"];
            unset($map["finance.user_department"]);
        }
        if(isset($map["finance.customer_name"])){//客户名称
            $map["customer.name"]=["like","%".$map["finance.customer_name"][1]."%"];
            unset($map["finance.customer_name"]);
        }
        if(isset($map["finance.crm_oonnxn"])){//原销售
            $map["contract.crm_oonnxn"]=["like","%".$map["finance.crm_oonnxn"][1]."%"];
            unset($map["finance.crm_oonnxn"]);
        }
        if(isset($map["finance.product_name"])){//产品类型
            $map["product_c.name"]=["like","%".$map["finance.product_name"][1]."%"];
            unset($map["finance.product_name"]);
        }
        //财务和超级管理员可以看所有，其他只能看对应自己和下级的
        $isFinance=db("admin_access")->where(["user_id"=>$user_id,"group_id"=>9])->find();//是否是财务
        $isButler = db('admin_access')->where(["user_id"=>$user_id,"group_id"=>32])->find();//管家
        if(!$isButler && !$isFinance && $user_id!=1){
            $childIds=$this->getChildIds($user_id);
            $childIds[]=$user_id;
            $map["finance.create_user_id"]=["in",$childIds];
        }
        $list = db('crm_finance')
            ->alias('finance')
            ->join('admin_user admin',"admin.id=finance.create_user_id","left")
            ->join('__CRM_CONTRACT__ contract',"contract.contract_id=finance.contract_id","left")
            ->join('__CRM_CUSTOMER__ customer',"customer.customer_id=finance.customer_id","left")
            ->join('__ADMIN_STRUCTURE__ structure',"structure.id=admin.structure_id","left")
            ->join('__CRM_CONTRACT_PRODUCT__ c_product',"c_product.contract_id=contract.contract_id","left")
            ->join('__CRM_PRODUCT__ product',"product.product_id=c_product.product_id","left")
            ->join('__CRM_PRODUCT_CATEGORY__ product_c',"product_c.category_id=product.category_id","left")
            ->where($map)
            ->field('finance.*,admin.id as admin_id,admin.username admin_username,admin.realname create_user_name
            ,contract.name contract_name,contract.num contract_num,contract.crm_oonnxn,contract.crm_ddisob,product_c.name product_name
            ,contract.crm_grvgkz,contract.remark contract_remark,contract.crm_wvgjii,contract.crm_wyffaz,contract.accounting_start_time,contract.accounting_end_time,contract.crm_yfvcld,
            contract.money cmoney,customer.name customer_name,structure.name user_department,product.product_id')
            ->order("finance.finance_id desc,finance.created_time desc")
            ->select();
        if($list){
            foreach ($list as $k=>$v){
                $list[$k]["check_status"]=$this->check_status[$v["check_status"]];
                if($v['crm_wvgjii'] == '记账') {
                    $confirmation = db("crm_contract_confirmation")->where(['contract_id'=>$v['contract_id'],'product_id'=>$v['product_id'],'name'=>'服务时间'])->find();
                    if(!empty($confirmation)){
                       $value_data = json_decode($confirmation['value_data']); 
                       $list[$k]["accounting_start_time"]=$value_data[0];
                       $list[$k]["accounting_end_time"]=$value_data[1];
                    }
                } else {
                    $list[$k]["accounting_start_time"]=$v["accounting_start_time"]?date("Y-m-d H:i:s",$v["accounting_start_time"]):"";
                    $list[$k]["accounting_end_time"]=$v["accounting_end_time"]?date("Y-m-d H:i:s",$v["accounting_end_time"]):"";
                }
                // $list[$k]["accounting_start_time"]=$v["accounting_start_time"]?date("Y-m-d H:i:s",$v["accounting_start_time"]):"";
                // $list[$k]["accounting_end_time"]=$v["accounting_end_time"]?date("Y-m-d H:i:s",$v["accounting_end_time"]):"";
                //calculate_month
                $list[$k]["calculate_month"]=(string)$v["calculate_month"];
            }
        }

        return $list;
    }

    /**
     * @param $user_id
     * 获取用户的所有下级
     */
    public function getChildIds($user_id){
        $childsArr=db("admin_user")->where(["parent_id"=>$user_id])->column("id");
        if($childsArr){
           foreach ($childsArr as $v){
               $arr=$this->getChildIds($v);
               if($arr) $childsArr=array_merge($childsArr,$arr);
           }
        }
        return $childsArr;
    }

    /**
     * @param $user_id
     * 获取获取下级一级
     */
    public function getChildId($user_id){
        $childsArr=db("admin_user")->where(["channel_parent_id"=>$user_id])->column("id");
        return $childsArr;
        db('admin_user')->where(['id' => $param['id']])->find();
    }

}